package com.css.fxfzysx.modules.vabStructure.repository.impl;

import com.css.fxfzysx.modules.cztDataMange.entity.CbhdInfoEntity;
import com.css.fxfzysx.modules.cztDataMange.entity.CbvInfoMatrixValueEntity;
import com.css.fxfzysx.modules.vabStructure.entity.CztBuildingMatrixEntity;
import com.css.fxfzysx.modules.vabStructure.param.BuildingStructureParam;
import com.css.fxfzysx.modules.vabStructure.repository.SvAnalysisRepository;
import com.css.fxfzysx.modules.vabStructure.service.impl.SvAnalysisServiceImpl;
import com.css.fxfzysx.modules.vabStructure.vo.GenerateVulnerabilityMatrixVo;
import com.css.fxfzysx.util.CreateGson;
import com.css.fxfzysx.util.PlatformObjectUtils;
import com.css.fxfzysx.web.PlatformSessionContext;
import com.css.fxfzysx.zcpt.sys.entity.SUser;
import com.css.fxfzysx.zcpt.sys.service.SUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;

@Repository
public class SvAnalysisRepositoryImpl implements SvAnalysisRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SUserService userService;


    @Override
    public List<GenerateVulnerabilityMatrixVo> getDataPercentageByNumbers(String ids,String intensity) {
        String sql = "select damage,count ( * ) num,round( 100 * count ( * ) * 1.0 / sum ( count ( * ) ) OVER ( ), 2 ) || '%' rate " +
                "from czt_building_history_damage where (select intensity) = '"+intensity+"'" +
                "and cbhd_id in ("+ids+") group by damage";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GenerateVulnerabilityMatrixVo.class));
    }

    @Override
    public HashMap<String, Object> queryBuildings(CbhdInfoEntity cbhdInfoEntity, int page, int size) {
        StringBuilder sql = new StringBuilder("select * from czt_building_history_damage where del_flag = '0'");
        if (cbhdInfoEntity.getRole().equals("1")){
            sql.append(" and create_user='"+ PlatformSessionContext.getUserID() +"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getProvince())) {
            sql.append(" and province ='" + cbhdInfoEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCity())) {
            sql.append(" and city ='" + cbhdInfoEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCounty())) {
            sql.append(" and county ='" + cbhdInfoEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getYears())) {
            sql.append(" and years ='" + cbhdInfoEntity.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cbhdInfoEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateEndTime())) {
            String endTime = cbhdInfoEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse0())) {
            sql.append(" and use0 ='" + cbhdInfoEntity.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse1())) {
            sql.append(" and use1 ='" + cbhdInfoEntity.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cbhdInfoEntity.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cbhdInfoEntity.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getFortification())) {
            sql.append(" and fortification ='" + cbhdInfoEntity.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getBasicLibrary())) {
            sql.append(" and basic_library ='" + cbhdInfoEntity.getBasicLibrary()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUserName())) {
            sql.append(" and create_user_name like'%" + cbhdInfoEntity.getUserName()+"%'");
        }
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        sql.append(orderSql).append(limitSql);
        List<CbhdInfoEntity> data = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
        for (CbhdInfoEntity datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUser())) {
                String user = userService.getSUser(datum.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if(PlatformObjectUtils.isNotEmpty(datum.getStructureType1())){
                    st.append("-"+datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getUse0()) && PlatformObjectUtils.isNotEmpty(datum.getUse0())) {
                StringBuilder use = new StringBuilder(datum.getUse0()).append("-").append(datum.getUse0());
                datum.setUse(use.toString());
            }
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }

    @Override
    public List<BuildingStructureParam> queryBuildingStructure(BuildingStructureParam buildingStructureParam) {
        StringBuilder sql = new StringBuilder("select * from(select cpi.cpvi_id as pid,province,city,county,structure_type0,structure_type1,fortification,purpose0,purpose1,years," +
                "cs as main_floor,cast(json_data as varchar) json_data,extends1 as  intensity,cpi.del_flag from czt_probability_vulnerability_info cpi" +
                "left join czt_probability_vulnerability_result on cpi.cpvi_id = czt_probability_vulnerability_result.cpvi_id" +
                "union all select cbi.cbcpi_id as pid,province,city,county,structure_type as structure_type0,structure_type1,fortification,purpose as purpose0,purpose1,years,total_floor as main_floor,conclusion as json_data,break_state as intensity,cbi.del_flag from czt_brick_concrete_param_info cbi" +
                "left join czt_brick_concrete_destroy_result on cbi.cbcpi_id = czt_brick_concrete_destroy_result.project_id" +
                "union all select chbpi_id as pid,province,city,county,structure_type as structure_type0,structure_type1,fortification,purpose as purpose0,purpose1,years,cs as mai_floor,forecast_results as json_data,intensity,chi.del_flag from czt_high_building_param_info chi) data_result where del_flag='0'");
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getProvince())) {
            sql.append(" and province = '" + buildingStructureParam.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getCity())) {
            sql.append(" and city = '" + buildingStructureParam.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getCounty())) {
            sql.append(" and county = '" + buildingStructureParam.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getStructureType1())) {
            sql.append(" and structure_type0 = '" + buildingStructureParam.getStructureType1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getStructureType1())) {
            sql.append(" and structure_type1 = '" + buildingStructureParam.getStructureType1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getFortification())) {
            sql.append(" and fortification = '" + buildingStructureParam.getFortification() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getPurpose())) {
            sql.append(" and purpose = '" + buildingStructureParam.getPurpose() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getPurpose1())) {
            sql.append(" and purpose1 = '" + buildingStructureParam.getPurpose1() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getYears())) {
            sql.append(" and years = '" + buildingStructureParam.getYears() + "'");
        }
        if (buildingStructureParam.getMainFloor() != 0) {
            sql.append(" and main_floor  = '" + buildingStructureParam.getMainFloor()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(buildingStructureParam.getIntensity())) {
            sql.append(" and intensity in ("+SvAnalysisServiceImpl.spilt(buildingStructureParam.getIntensity())+")");
        }
        List<BuildingStructureParam> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingStructureParam.class));
        return result;
    }

    @Override
    public HashMap<String, Object> queryPredictiveData(CbhdInfoEntity cbhdInfoEntity, int page, int size) {
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT NUMBER , cbi.cbcpi_id AS pid, province, city, county, structure_type, structure_type1, " +
//                "fortification, purpose AS use0, purpose1 AS use1, years, data_type, total_floor AS cs, basic_library, " +
//                "cbi.del_flag, cbi.create_user, cbi.create_time FROM czt_brick_concrete_param_info cbi UNION ALL SELECT NUMBER , " +
//                "chbpi_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, " +
//                "purpose1 AS use1, years, data_type, cs, basic_library, chi.del_flag, chi.create_user, chi.create_time FROM " +
//                "czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, province, city, county, " +
//                "structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, " +
//                "basic_library, cr.del_flag, cr.create_user_id AS create_user, cr.create_time FROM czt_crs_calentirety_result cr) " +
//                "result_data WHERE del_flag = '0'");
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT NUMBER, cbi.cbcbi_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, total_floor AS cs, basic_library, cbi.del_flag, cbi.create_user, cbi.create_time FROM czt_brick_concrete_building_info cbi UNION ALL SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, chi.del_flag, chi.create_user, chi.create_time FROM czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, cr.del_flag, cr.create_user_id AS create_user, cr.create_time FROM czt_crs_calentirety_result cr) result_data WHERE del_flag = '0'");
        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT NUMBER , cbi.cbcbi_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, total_floor AS cs, basic_library, calculate_or_not, cbi.del_flag, cbi.create_user, cbi.create_time , user_name FROM czt_brick_concrete_building_info cbi UNION ALL SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, calculate_or_not, chi.del_flag, chi.create_user, chi.create_time, user_name FROM czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, calculate_or_not, cr.del_flag, cr.create_user_id AS create_user, cr.create_time, create_user_name as user_name FROM czt_crs_calentirety_result cr) result_data WHERE del_flag = '0'");
        if (cbhdInfoEntity.getRole().equals("1")){
            sql.append(" and create_user='"+ PlatformSessionContext.getUserID() +"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getProvince())) {
            sql.append(" and province ='" + cbhdInfoEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCity())) {
            sql.append(" and city ='" + cbhdInfoEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCounty())) {
            sql.append(" and county ='" + cbhdInfoEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getYears())) {
            sql.append(" and years ='" + cbhdInfoEntity.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getDamage())) {
            sql.append(" and damage like '%" + cbhdInfoEntity.getDamage() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cbhdInfoEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateEndTime())) {
            String endTime = cbhdInfoEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cbhdInfoEntity.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cbhdInfoEntity.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getFortification())) {
            sql.append(" and fortification ='" + cbhdInfoEntity.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCalculateOrNot())) {
            sql.append(" and calculate_or_not ='" + cbhdInfoEntity.getCalculateOrNot()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse0())) {
            sql.append(" and use0 ='" + cbhdInfoEntity.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse1())) {
            sql.append(" and use1 ='" + cbhdInfoEntity.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getBasicLibrary())) {
            sql.append(" and basic_library ='" + cbhdInfoEntity.getBasicLibrary()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUserName())) {
            sql.append(" and user_name like'%" + cbhdInfoEntity.getUserName()+"%'");
        }
        StringBuilder orderSql = new StringBuilder(" ORDER BY create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(orderSql).append(limitSql);
        List<CbhdInfoEntity> data = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
        for (CbhdInfoEntity datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUser())) {
                String user = userService.getSUser(datum.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if(PlatformObjectUtils.isNotEmpty(datum.getStructureType1())){
                    st.append("-"+datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getUse0()) && PlatformObjectUtils.isNotEmpty(datum.getUse0())) {
                StringBuilder use = new StringBuilder(datum.getUse0()).append("-").append(datum.getUse0());
                datum.setUse(use.toString());
            }
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }

    @Override
    public HashMap<String, Object> queryFilterData(CbhdInfoEntity cbhdInfoEntity, int page, int size) {
//        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT NUMBER , pid, '2' AS data_sv_type, province, city, county, structure_type, " +
//                "structure_type1, fortification, use0, use1, years, data_type, cs, basic_library, del_flag, create_user, " +
//                "create_time FROM ( SELECT NUMBER , cbi.cbcpi_id AS pid, province, city, county, structure_type AS structure_type, " +
//                "structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, total_floor AS cs, " +
//                "basic_library, cbi.del_flag, cbi.create_user, cbi.create_time FROM czt_brick_concrete_param_info cbi UNION ALL " +
//                "SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, " +
//                "fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, chi.del_flag, " +
//                "chi.create_user, chi.create_time FROM czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, " +
//                "province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, " +
//                "data_type, cs, basic_library, cr.del_flag, cr.create_user_id AS create_user, cr.create_time FROM " +
//                "czt_crs_calentirety_result cr) data_result UNION ALL SELECT DISTINCT NUMBER , cbhd_id AS pid, '1' AS data_sv_type, " +
//                "province, city, county, structure_type AS structure_type, structure_type1, fortification, use0, use1, years, " +
//                "data_type, cs, basic_library, del_flag, create_user, create_time FROM " +
//                "( SELECT *, ROW_NUMBER ( ) OVER ( PARTITION BY ( NUMBER ) ORDER BY ( create_time ) ) AS s1 FROM " +
//                "czt_building_history_damage ) s WHERE s1 = 1 ) result_data WHERE del_flag = '0'");
        StringBuilder sql = new StringBuilder("SELECT * FROM( SELECT DISTINCT NUMBER , pid, '2' AS data_sv_type, province, city, county, structure_type, structure_type1, fortification, use0, use1, years, data_type, cs, basic_library, calculate_or_not, del_flag, create_user, create_time, user_name FROM ( SELECT NUMBER , cbi.cbcbi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, total_floor AS cs, basic_library, calculate_or_not, cbi.del_flag, cbi.create_user, cbi.create_time, user_name FROM czt_brick_concrete_building_info cbi UNION ALL SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type AS structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, calculate_or_not, chi.del_flag, chi.create_user, chi.create_time, user_name FROM czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, province, city, county, structure_type, structure_type1, fortification, purpose AS use0, purpose1 AS use1, years, data_type, cs, basic_library, calculate_or_not, cr.del_flag, cr.create_user_id AS create_user, cr.create_time, cr.create_user_name as user_name FROM czt_crs_calentirety_result cr) data_result UNION ALL SELECT NUMBER , cbhd_id AS pid, '1' AS data_sv_type, province, city, county, structure_type AS structure_type, structure_type1, fortification, use0, use1, years, data_type, cs, basic_library, '-' AS calculate_or_not, del_flag, create_user, create_time, create_user_name as user_name FROM czt_building_history_damage ) result_data WHERE del_flag = '0'");
        if (cbhdInfoEntity.getRole().equals("1")){
            sql.append(" and create_user='"+ PlatformSessionContext.getUserID() +"'");
        }
        if(!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getDataSvType())){
            sql.append(" and data_sv_type ='" + cbhdInfoEntity.getDataSvType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getProvince())) {
            sql.append(" and province ='" + cbhdInfoEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCity())) {
            sql.append(" and city ='" + cbhdInfoEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCounty())) {
            sql.append(" and county ='" + cbhdInfoEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getYears())) {
            sql.append(" and years ='" + cbhdInfoEntity.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cbhdInfoEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCreateEndTime())) {
            String endTime = cbhdInfoEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cbhdInfoEntity.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cbhdInfoEntity.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getFortification())) {
            sql.append(" and fortification ='" + cbhdInfoEntity.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getCalculateOrNot())) {
            sql.append(" and calculate_or_not ='" + cbhdInfoEntity.getCalculateOrNot()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse0())) {
            sql.append(" and use0 ='" + cbhdInfoEntity.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUse1())) {
            sql.append(" and use1 ='" + cbhdInfoEntity.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getBasicLibrary())) {
            sql.append(" and basic_library ='" + cbhdInfoEntity.getBasicLibrary()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cbhdInfoEntity.getUserName())) {
            sql.append(" and user_name like'%" + cbhdInfoEntity.getUserName()+"%'");
        }
        StringBuilder orderSql = new StringBuilder("order by create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(orderSql).append(limitSql);
        List<CbhdInfoEntity> data = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
        for (CbhdInfoEntity datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUser())) {
                String user = userService.getSUser(datum.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if(PlatformObjectUtils.isNotEmpty(datum.getStructureType1())){
                    st.append("-"+datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getUse0()) && PlatformObjectUtils.isNotEmpty(datum.getUse0())) {
                StringBuilder use = new StringBuilder(datum.getUse0()).append("-").append(datum.getUse0());
                datum.setUse(use.toString());
            }
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }


    @Override
    public List<GenerateVulnerabilityMatrixVo> getDataPredictiveByNumbers(String ids, String intensity,Integer type) {
        String sql = "";
        String tableName = "forecast_results6";
        if(intensity.equals("7度")){
            tableName = "forecast_results7";
        }else if(intensity.equals("8度")){
            tableName = "forecast_results8";
        }else if(intensity.equals("9度")){
            tableName = "forecast_results9";
        }else if(intensity.equals("10度")){
            tableName = "forecast_results10";
        }
        if(type == 2){
            sql = "SELECT damage, COUNT( *) num, ROUND( 100 * COUNT ( * ) * 1.0 / SUM ( COUNT ( * ) ) OVER ( ), 2 ) || '%' rate FROM ( SELECT cbi.cbcbi_id AS pid, NUMBER, province, city, county, structure_type AS structure_type0, structure_type1, fortification, purpose AS purpose0, purpose1, years, total_floor AS main_floor, regexp_split_to_table( conclusion, ',' ) AS damage, break_state AS intensity, cbi.del_flag, cbi.create_user FROM czt_brick_concrete_building_info cbi LEFT JOIN czt_brick_concrete_destroy_result ON cbi.cbcbi_id = czt_brick_concrete_destroy_result.project_id GROUP BY damage, pid, intensity UNION ALL SELECT chbpi_id AS pid, NUMBER, province, city, county, structure_type AS structure_type0, structure_type1, fortification, purpose AS purpose0, purpose1, years, cs AS mai_floor, "+tableName+" AS damage, "+"'"+intensity+"'"+" AS intensity, chi.del_flag, chi.create_user FROM czt_high_building_param_info chi UNION ALL SELECT cr.cal_id AS pid, NUMBER, province, city, county, structure_type AS structure_type0, structure_type1, fortification, purpose AS purpose0, purpose1, years, cs AS main_floor, regexp_split_to_table ( damage_condition, ';' ) damage, seismic_intensity as intensity, cr.del_flag, cr.create_user_id AS create_user FROM czt_crs_calentirety_result cr LEFT JOIN czt_crs_calmatrix_result ccr ON cr.cal_id = ccr.entirety_id WHERE cr.del_flag = '0' GROUP BY NUMBER, cr.cal_id, intensity , damage ) data_result  WHERE del_flag = '0' " +
                    "and pid in ("+ids+") and intensity = '"+intensity+"' GROUP BY damage";
        }
        if(type == 3){
            sql = "SELECT damage, COUNT( *) num, round( 100 * COUNT ( * ) * 1.0 / SUM ( COUNT ( * ) ) OVER ( ), 2 ) || '%' rate FROM ( SELECT pid, NUMBER, '1' AS TYPE, province, city, county, structure_type0, structure_type1, fortification, purpose0, purpose1, years, main_floor, damage, intensity, del_flag, create_user FROM ( SELECT cbi.cbcbi_id AS pid, NUMBER, province, city, county, structure_type AS structure_type0, structure_type1, fortification, purpose AS purpose0, purpose1, years, total_floor AS main_floor, regexp_split_to_table( conclusion, ',' ) AS damage, break_state AS intensity, cbi.del_flag, cbi.create_user FROM czt_brick_concrete_building_info cbi LEFT JOIN czt_brick_concrete_destroy_result ON cbi.cbcbi_id = czt_brick_concrete_destroy_result.project_id GROUP BY damage, pid, intensity UNION ALL SELECT chbpi_id AS pid, NUMBER, province, city, county, structure_type AS structure_type0, structure_type1, fortification, purpose AS purpose0, purpose1, years, cs AS mai_floor, "+tableName+" AS damage, "+"'"+intensity+"'"+" AS intensity, chi.del_flag, chi.create_user FROM czt_high_building_param_info chi UNION ALL SELECT cr.cal_id AS pid, NUMBER, province, city, county, structure_type AS structure_type0, structure_type1, fortification, purpose AS purpose0, purpose1, years, cs AS main_floor, regexp_split_to_table ( damage_condition, ';' ) damage, seismic_intensity as intensity, cr.del_flag, cr.create_user_id AS create_user FROM czt_crs_calentirety_result cr LEFT JOIN czt_crs_calmatrix_result ccr ON cr.cal_id = ccr.entirety_id WHERE cr.del_flag = '0' GROUP BY NUMBER, cr.cal_id, damage, intensity ) data_result UNION ALL SELECT cbhd_id AS pid, NUMBER, '2' AS TYPE, province, city, county, structure_type AS structure_type0, structure_type1, fortification, use0 AS purpose0, use1 AS purpose1, years, cs AS main_floor, damage, SUBSTRING ( intensity, 0, 3 ) AS intensity, del_flag, create_user FROM ( SELECT *, ROW_NUMBER ( ) OVER ( PARTITION BY ( NUMBER ) ORDER BY ( create_time ) ) AS s1 FROM czt_building_history_damage ) s ) result_data  WHERE del_flag = '0' and pid " +
                    "in ("+ids+") and intensity = '"+intensity+"' GROUP BY damage";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GenerateVulnerabilityMatrixVo.class));
    }

    @Override
    public List<CbhdInfoEntity> getEarthquakeDamageData(String ids,Integer type) {
        String sql = "";
        if(type == 1){
//            sql = "select s.* from (select *,row_number() over(partition by (number) order by (create_time))  as s1 from czt_building_history_damage) s " +
//                    "where del_flag='0' and s1=1 and number in ("+numbers+") ";
            sql = "select * from czt_building_history_damage where cbhd_id in ("+ids+")";
        }
        if(type == 2){
            sql = "SELECT * FROM( SELECT NUMBER, cbi.cbcbi_id AS pid, province, city, county, structure_type, structure_type1, cbcdr.break_state AS intensity, fortification, purpose AS use0, purpose1 AS use1, years, total_floor AS cs, cbi.del_flag FROM czt_brick_concrete_building_info cbi LEFT JOIN czt_brick_concrete_destroy_result cbcdr ON cbi.cbcbi_id = cbcdr.project_id UNION ALL SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type, structure_type1, fortification AS intensity, fortification, purpose AS use0, purpose1 AS use1, years, cs, chi.del_flag FROM czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, province, city, county, structure_type, structure_type1, ccr.seismic_intensity AS intensity, fortification, purpose AS use0, purpose1 AS use1, years, cs, cr.del_flag FROM czt_crs_calentirety_result cr LEFT JOIN czt_crs_calmatrix_result ccr ON cr.cal_id = ccr.entirety_id) result_data WHERE del_flag = '0' and pid in ("+ids+")";
        }
        if(type == 3){
            sql = "SELECT * FROM( SELECT DISTINCT NUMBER , pid, '2' AS data_sv_type, province, city, county, structure_type, structure_type1, intensity, fortification, use0, use1, years, cs, del_flag FROM ( SELECT NUMBER , cbi.cbcbi_id AS pid, province, city, county, structure_type, structure_type1, cbcdr.break_state AS intensity, fortification, purpose AS use0, purpose1 AS use1, years, total_floor AS cs, cbi.del_flag FROM czt_brick_concrete_building_info cbi LEFT JOIN czt_brick_concrete_destroy_result cbcdr ON cbi.cbcbi_id = cbcdr.project_id UNION ALL SELECT NUMBER , chbpi_id AS pid, province, city, county, structure_type, structure_type1, fortification AS intensity, fortification, purpose AS use0, purpose1 AS use1, years, cs, chi.del_flag FROM czt_high_building_param_info chi UNION ALL SELECT NUMBER , cr.cal_id AS pid, province, city, county, structure_type, structure_type1, ccr.seismic_intensity AS intensity, fortification, purpose AS use0, purpose1 AS use1, years, cs, cr.del_flag FROM czt_crs_calentirety_result cr LEFT JOIN czt_crs_calmatrix_result ccr ON cr.cal_id = ccr.entirety_id) data_result UNION ALL SELECT NUMBER , cbhd_id AS pid, '1' AS data_sv_type, province, city, county, structure_type AS structure_type, structure_type1, intensity, fortification, use0, use1, years, cs, del_flag FROM czt_building_history_damage ) result_data  WHERE del_flag='0' and pid in ("+ids+")";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CbhdInfoEntity.class));
    }

    @Override
    public void saveBuildingMatrix(CztBuildingMatrixEntity cztBuildingMatrix) {
        jdbcTemplate.update("insert into czt_building_matrix  (matrix_id,number,province,city,county,structure_type,structure_type1,fortification,years,cs,use0,use1,create_user,create_time,basic_library,del_flag,data_sources_ids,matrix_type,data_type,is_specification,data_sources_nums,user_name) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                cztBuildingMatrix.getMatrixId(),cztBuildingMatrix.getNumber(),cztBuildingMatrix.getProvince(),cztBuildingMatrix.getCity(),cztBuildingMatrix.getCounty(),cztBuildingMatrix.getStructureType(),cztBuildingMatrix.getStructureType1(),cztBuildingMatrix.getFortification(),cztBuildingMatrix.getYears(),cztBuildingMatrix.getCs(),cztBuildingMatrix.getUse0(),cztBuildingMatrix.getUse1(),cztBuildingMatrix.getCreateUser(),cztBuildingMatrix.getCreateTime(),cztBuildingMatrix.getBasicLibrary(),cztBuildingMatrix.getDelFlag(),cztBuildingMatrix.getDataSourcesIds(),cztBuildingMatrix.getMatrixType(),cztBuildingMatrix.getDataType(),cztBuildingMatrix.getIsSpecification(),cztBuildingMatrix.getDataSourcesNums(),cztBuildingMatrix.getUserName());
    }

    @Override
    public HashMap<String, Object> getVulnerabilityMatrix(CztBuildingMatrixEntity cztBuildingMatrixEntity, int page, int size) {
        StringBuilder sql = new StringBuilder("select * from czt_building_matrix where del_flag = '0'");
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getProvince())) {
            sql.append(" and province ='" + cztBuildingMatrixEntity.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getCity())) {
            sql.append(" and city ='" + cztBuildingMatrixEntity.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getCounty())) {
            sql.append(" and county ='" + cztBuildingMatrixEntity.getCounty() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getYears())) {
            sql.append(" and years ='" + cztBuildingMatrixEntity.getYears() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getCreateStartTime())) {
            sql.append(" and create_time >= '" + cztBuildingMatrixEntity.getCreateStartTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getCreateEndTime())) {
            String endTime = cztBuildingMatrixEntity.getCreateEndTime() + " 23:59:59";
            sql.append(" and create_time <='" + endTime+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getStructureType())) {
            sql.append(" and structure_type ='" + cztBuildingMatrixEntity.getStructureType()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getStructureType1())) {
            sql.append(" and structure_type1 ='" + cztBuildingMatrixEntity.getStructureType1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getFortification())) {
            sql.append(" and fortification ='" + cztBuildingMatrixEntity.getFortification()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getUse0())) {
            sql.append(" and use0 ='" + cztBuildingMatrixEntity.getUse0()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getUse1())) {
            sql.append(" and use1 ='" + cztBuildingMatrixEntity.getUse1()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(cztBuildingMatrixEntity.getBasicLibrary())) {
            sql.append(" and basic_library ='" + cztBuildingMatrixEntity.getBasicLibrary()+"'");
        }
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CbhdInfoEntity.class)).size();
        StringBuilder limitSql = new StringBuilder(" limit " + size + " offset " + (page - 1) * size);
        sql.append(orderSql).append(limitSql);
        List<CztBuildingMatrixEntity> data = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(CztBuildingMatrixEntity.class));
        for (CztBuildingMatrixEntity datum : data) {
            if (PlatformObjectUtils.isNotEmpty(datum.getCreateUser())) {
                String user = userService.getSUser(datum.getCreateUser());
                SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
                datum.setUserName(sUser.getUserName());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getProvince()) && PlatformObjectUtils.isNotEmpty(datum.getCity()) && PlatformObjectUtils.isNotEmpty(datum.getCounty())) {
                StringBuilder region = new StringBuilder(datum.getProvince()).append("-").append(datum.getCity()).append("-").append(datum.getCounty());
                datum.setRegion(region.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getStructureType())) {
                StringBuilder st = new StringBuilder(datum.getStructureType());
                if(PlatformObjectUtils.isNotEmpty(datum.getStructureType1())){
                    st.append("-"+datum.getStructureType1());
                }
                datum.setSt(st.toString());
            }
            if (PlatformObjectUtils.isNotEmpty(datum.getUse0()) && PlatformObjectUtils.isNotEmpty(datum.getUse0())) {
                StringBuilder use = new StringBuilder(datum.getUse0()).append("-").append(datum.getUse0());
                datum.setUse(use.toString());
            }
        }
        HashMap<String, Object> resultMap = new HashMap();
        resultMap.put("total", total);
        resultMap.put("rows", data);
        return resultMap;
    }

    @Override
    public CztBuildingMatrixEntity getBuildingMatrixById(String matrixId) {
        CztBuildingMatrixEntity buildingMatrixEntity = null;
        String sql = "select * from czt_building_matrix where matrix_id = '"+matrixId+"'";
        List<CztBuildingMatrixEntity> list = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CztBuildingMatrixEntity.class));
        if(list.size()>0){
            buildingMatrixEntity = list.get(0);
        }
        return buildingMatrixEntity;
    }

    @Override
    public void moveBasicLibraryById(String dataId,String operate) {
        String sql = "update czt_building_matrix set basic_library= '"+operate+"' where matrix_id = '"+dataId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer getBuildingMatrix(String ids) {
        String sql = "select count(*) from czt_building_matrix where data_sources_ids ='"+ids+"' and del_flag = '0'";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    @Override
    public List<CztBuildingMatrixEntity> queryBuildingMatrix(CztBuildingMatrixEntity entity) {
        try {
            String sql = "select * from czt_building_matrix where province = '"+entity.getProvince()+"' and city = '"+entity.getCity()+"' and county = '"+entity.getCounty()+"' and structure_type = '"+entity.getStructureType()+"' and structure_type1 = '"+entity.getStructureType1()+"' and years = '"+entity.getYears()+"' and fortification = '"+entity.getFortification()+"' and use0 = '"+entity.getUse0()+"' and use1 = '"+entity.getUse1()+"' and cs = '"+entity.getCs()+"'";
            return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CztBuildingMatrixEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<CbvInfoMatrixValueEntity> getInfoMatrixById(String pId) {
        try {
            String sql = "select * from czt_building_vulnerability_info_matrix_value where cbvi_id = '"+pId+"'";
            return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(CbvInfoMatrixValueEntity.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public void deleteBasicLibrary(String id) {
        String sql = "update czt_building_matrix set del_flag = 1 where matrix_id = '"+id+"'";
        jdbcTemplate.update(sql);
    }
}
